package com.aat.dxfy.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import com.aat.dizena.utils.Atools;

public class Xls2DbTest01 {

	public static void main(String[] args) throws IOException {
		File xls = new File("D:\\shici.xls");
		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(xls));
		HSSFSheet sheet = wb.getSheetAt(0);

		int rowNum = 0;
		String bookidTmp = null;
		for (Row row : sheet) {
			for (Cell cell : row) {
				try {
					String str = cell.getRichStringCellValue().getString();
					String[] ss1 = str.split(",\"");
					String bookname = ss1[0].replace("《", "").replace("》", "");
					String bookicon = cleanFuhao(ss1[1]);
					String author = cleanFuhao(ss1[2]).replace("作者：", "");
					String intro = cleanFuhao(ss1[3]);
					String context = cleanFuhao(ss1[6]);
					String ss = cleanFuhao(ss1[5]).replaceFirst("第", "").replaceFirst("回", "");

					String[] sss = ss.split("　");
					Integer chapnum = cleanXuhao(sss[0]);
					String title = sss[1] + "　" + sss[2];

					//
					String bookid = addBook(bookname, bookicon, author, intro);
					if (bookid != null) {
						bookidTmp = bookid;
					} else {
						bookid = bookidTmp;
					}
					addChap(bookid, chapnum, title, context);
					//
				} catch (Exception e) {
				}
			}
			rowNum++;
			System.out.println(rowNum);
			/*
			 if(rowNum >2 ) {
				break;
			}
			 */
		}
		wb.close();
	}

	public static String addBook(String bookname, String bookicon, String author, String intro) {
		String id = queryBook(bookname, author);
		if (id == null) {
			id = Atools.getOneKeyS();
			String sql = "insert into book_book(`id`,`bookname`, `bookicon`,"
					+ "`author`, `intro`, `pubtime`, `ischap`, `isover`,"
					+ "`bookurl`, `booktags`,`bookCome`) VALUES('"+id+"','" + bookname + "','" + bookicon + "'," + "'"
					+ author + "','" + intro + "','1990-01-01 00:00:00','0','0'," 
					+ "'http://www.shicimingju.com/book/sanguoyanyi.html','历史,名著','诗词名句网')";
			System.out.println(sql);
			insert(sql);
		}
		return id;
	}
	
	public static void addChap(String bookid, Integer chapnum,String title,String context) {
		String id = queryChap(bookid, chapnum);
		if(id==null) {
			id = Atools.getOneKeyS();
			String sql = "insert into book_chap(`id`, `bookid`, `chapnum`, `chapname`, `pubtime`, `chaptext`,`ts`)"
					+ " VALUES('"+id+"','" + bookid + "','" + chapnum + "',"
					+ "'"+ title + "','1990-01-01 00:00:00'," 
					+ "'" + context + "',now())";
			System.out.println(sql);
			insert(sql);
		}
	}

	public static void insert(String sql) {
		try {
			Connection conn = getConnection();
			conn.createStatement().execute(sql);
			conn.close();
		} catch (SQLException e) {
		}
	}

	public static String queryBook(String bookname, String author) {
		String id = null;
		try {
			Connection conn = getConnection();
			ResultSet rs = conn.createStatement().executeQuery(
					"select id from book_book " + "where bookname='" + bookname + "' and author= '" + author + "'");
			rs.next();
			id = rs.getString(1);
			conn.close();
		} catch (SQLException e) {
		}
		return id;
	}
	
	public static String queryChap(String bookid, Integer chapnum) {
		String id = null;
		try {
			Connection conn = getConnection();
			ResultSet rs = conn.createStatement().executeQuery(
					"select id from book_chap " + "where bookid='" + bookid + "' and chapnum= '" + chapnum + "'");
			rs.next();
			id = rs.getString(1);
			conn.close();
		} catch (SQLException e) {
		}
		return id;
	}

	private static Connection getConnection() {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/dizenabook?useUnicode=true&characterEncoding=utf8";
		String user = "root";
		String passwd = "root";
		try {
			Class.forName(driver);
			return DriverManager.getConnection(url, user, passwd);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("驱动加载错误或者没有找到驱动包!");
			return null;
		}
	}

	public static String cleanFuhao(String tmp) {
		if (tmp.endsWith("\"")) {
			tmp = tmp.substring(0, tmp.length() - 1);
		}
		return tmp;
	}

	public static int cleanXuhao(String tmp) {
		return chineseNumber2Int(tmp);
	}

	public static String clean(String sss) {
		return sss.replaceAll("[\\pP\\p{Punct}]", "");
	}

	/**
	 * 中文數字转阿拉伯数组【十万九千零六十 --> 109060】
	 * 
	 * @author 雪见烟寒
	 * @param chineseNumber
	 * @return
	 */
	private static int chineseNumber2Int(String chineseNumber) {
		int result = 0;
		int temp = 1;// 存放一个单位的数字如：十万
		int count = 0;// 判断是否有chArr
		char[] cnArr = new char[] { '一', '二', '三', '四', '五', '六', '七', '八', '九' };
		char[] chArr = new char[] { '十', '百', '千', '万', '亿' };
		for (int i = 0; i < chineseNumber.length(); i++) {
			boolean b = true;// 判断是否是chArr
			char c = chineseNumber.charAt(i);
			for (int j = 0; j < cnArr.length; j++) {// 非单位，即数字
				if (c == cnArr[j]) {
					if (0 != count) {// 添加下一个单位之前，先把上一个单位值添加到结果中
						result += temp;
						temp = 1;
						count = 0;
					}
					// 下标+1，就是对应的值
					temp = j + 1;
					b = false;
					break;
				}
			}
			if (b) {// 单位{'十','百','千','万','亿'}
				for (int j = 0; j < chArr.length; j++) {
					if (c == chArr[j]) {
						switch (j) {
						case 0:
							temp *= 10;
							break;
						case 1:
							temp *= 100;
							break;
						case 2:
							temp *= 1000;
							break;
						case 3:
							temp *= 10000;
							break;
						case 4:
							temp *= 100000000;
							break;
						default:
							break;
						}
						count++;
					}
				}
			}
			if (i == chineseNumber.length() - 1) {// 遍历到最后一个字符
				result += temp;
			}
		}
		return result;
	}

}
